﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace Plane
{ 
    internal class UserDbHelper
    {
        //数据库链接字符串
        private const string ConnectionString = "Data Source=users.db;Version=3;";

        // 初始化数据库
        public static void InitializeDatabase()
        {
            //if (File.Exists("users.db"))
            //{
            //    File.Delete("users.db");
            //}
            // 如果文件不存在就创建数据库文件
            if (!File.Exists("users.db"))
            {
                SQLiteConnection.CreateFile("users.db");

                //与数据库进行链接
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    // 创建用户表
                    string createTableQuery = @"
                CREATE TABLE IF NOT EXISTS Users (
                    Username TEXT NOT NULL UNIQUE,
                    Password TEXT NOT NULL,
                    Money INTEGER,
                    HighestScore INTEGER,
                    RecoveryAmount INTEGER,  
                    BulletAmount INTEGER,
                    PlaneSKin TEXT NOT NULL,
                    KeyUp TEXT NOT NULL,
                    KeyDown TEXT NOT NULL,
                    KeyLeft TEXT NOT NULL,
                    KeyRight TEXT NOT NULL,
                    KeyFire TEXT NOT NULL,
                    SoundVolume INTEGER
                )";

                    //将SQL语句与数据库链接
                    using (var command = new SQLiteCommand(createTableQuery, connection))
                    {
                        //创建表，不返回值
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
        
        //添加用户
        public static bool AddUser(string username, string password,int money,int highestscore,int recoveryamount,int bulletamount,string planeskin,string keyup,string keydown,string keyleft,string keyright,string keyfire,int soundvolume)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    //插入语句
                    string query = @"
                INSERT INTO Users (Username, Password, Money, HighestScore, RecoveryAmount, BulletAmount, PlaneSkin, KeyUp, KeyDown, KeyLeft, KeyRight, KeyFire, SoundVolume)
                VALUES (@Username, @Password, @Money, @HighestScore, @RecoveryAmount, @BulletAmount, @PlaneSkin, @KeyUp, @KeyDown, @KeyLeft, @KeyRight, @KeyFire, @SoundVolume)";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@Password", password);
                        command.Parameters.AddWithValue("@Money", money);
                        command.Parameters.AddWithValue("@HighestScore", highestscore);
                        command.Parameters.AddWithValue("@RecoveryAmount", recoveryamount);
                        command.Parameters.AddWithValue("@BulletAmount", bulletamount);
                        command.Parameters.AddWithValue("@PlaneSkin", planeskin);
                        command.Parameters.AddWithValue("@KeyUp", keyup);
                        command.Parameters.AddWithValue("@KeyDown", keydown);
                        command.Parameters.AddWithValue("@KeyLeft", keyleft);
                        command.Parameters.AddWithValue("@KeyRight", keyright);
                        command.Parameters.AddWithValue("@KeyFire", keyfire);
                        command.Parameters.AddWithValue("@SoundVolume", soundvolume);

                        //受影响的行数大于0代表插入成功
                        return command.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"数据库错误: {ex.Message}");
                return false;
            }
        }

        //通过用户名来获取用户信息,返回一个可空的元组
        public static (string Password, int Money, int HighestScore, int RecoveryAmount, int BulletAmount, string PlaneSkin,string KeyUp,string KeyDown,string KeyLeft,string KeyRight,string KeyFire,int SoundVolume) GetUserInformationByUsername(string username)
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                //返回第一条用户名匹配的用户信息的请求
                string query = @"
            SELECT Password, Money, HighestScore, RecoveryAmount, BulletAmount, PlaneSkin, KeyUp, KeyDown, KeyLeft, KeyRight, KeyFire, SoundVolume
            FROM Users 
            WHERE Username = @Username 
            LIMIT 1";

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Username", username);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return (
                                reader["Password"].ToString(),
                                Convert.ToInt32(reader["Money"]),
                                Convert.ToInt32(reader["HighestScore"]),
                                Convert.ToInt32(reader["RecoveryAmount"]),
                                Convert.ToInt32(reader["BulletAmount"]),
                                reader["PlaneSkin"].ToString(),
                                reader["KeyUp"].ToString(),
                                reader["KeyDown"].ToString(),
                                reader["KeyLeft"].ToString(),
                                reader["KeyRight"].ToString(),
                                reader["KeyFire"].ToString(),
                                Convert.ToInt32(reader["SoundVolume"])
                            );
                        }
                        //没有找到用户的情况就返回信息错误明显的元组以进行判断
                        return ("",0,0,0,0,"","","","","","",0);
                    }
                }
            }
        }

        //更新用户的密码
        public static void UpdatePassword(string username, string newPassword)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET Password = @NewPassword 
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewPassword", newPassword);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新密码失败: {ex.Message}");
            }
        }

        //更新用户的金币数
        public static void UpdateMoney(string username,int newMoney)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET Money = @NewMoney 
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewMoney", newMoney);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新金币数失败: {ex.Message}");
            }
        }

        //更新用户的最高分数
        public static void UpdateHighestScore(string username, int newHighestScore)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET HighestScore = @NewHighestScore 
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewHighestScore", newHighestScore);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新最高分数失败: {ex.Message}");
            }
        }

        //更新用户的恢复量
        public static void UpdateRecoveryAmount(string username, int newRecoveryAmount)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET RecoveryAmount = @NewRecoveryAmount
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewRecoveryAmount", newRecoveryAmount);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新恢复量失败: {ex.Message}");
            }
        }

        //更新用户的子弹数量
        public static void UpdateBulletAmount(string username, int newBulletAmount)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET BulletAmount = @NewBulletAmount
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewBulletAmount", newBulletAmount);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新子弹数量失败: {ex.Message}");
            }
        }

        //更新用户的飞机皮肤
        public static void UpdatePlaneSkin(string username, string newPlaneSkin)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET PlaneSkin = @NewPlaneSkin
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewPlaneSkin", newPlaneSkin);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新飞机皮肤失败: {ex.Message}");
            }
        }

        //更新用户的向上键位
        public static void UpdateKeyUp(string username, string newKeyUp)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET KeyUp = @NewKeyUp
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewKeyUp", newKeyUp);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新向上键位失败: {ex.Message}");
            }
        }

        //更新用户的向下键位
        public static void UpdateKeyDown(string username, string newKeyDown)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET KeyDown = @NewKeyDown
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewKeyDown", newKeyDown);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新向下键位失败: {ex.Message}");
            }
        }

        //更新用户的向左键位
        public static void UpdateKeyLeft(string username, string newKeyLeft)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET KeyLeft = @NewKeyLeft
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewKeyLeft", newKeyLeft);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新向左键位失败: {ex.Message}");
            }
        }

        //更新用户的向右键位
        public static void UpdateKeyRight(string username, string newKeyRight)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET KeyRight = @NewKeyRight
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewKeyRight", newKeyRight);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新向右键位失败: {ex.Message}");
            }
        }

        //更新用户的开火键位
        public static void UpdateKeyFire(string username, string newKeyFire)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET KeyFire = @NewKeyFire
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewKeyFire", newKeyFire);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新开火键位失败: {ex.Message}");
            }
        }

        //更新用户的初始音量
        public static void UpdateSoundVolume(string username, int newSoundVolume)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string query = @"
            UPDATE Users 
            SET SoundVolume = @NewSoundVolume
            WHERE Username = @Username";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.Parameters.AddWithValue("@NewSoundVolume", newSoundVolume);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex) //异常处理
            {
                MessageBox.Show($"更新开火键位失败: {ex.Message}");
            }
        }


        //删除单个用户
        public static void DeleteUser(string username)
        {
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    string sql = "DELETE FROM Users WHERE Username = @Username";

                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@Username", username);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show($"删除用户失败: {ex.Message}");
            }
        }

        //查询分数排名前三名的用户，并返回用户名和得分
        public static List<(string Username, int HighestScore)> GetTopThreeScorers()
        {
            var topScorers = new List<(string Username, int HighestScore)>();
            try
            {
                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    // 查询语句，按分数降序排序并取前三条记录
                    string query = @"
                    SELECT Username, HighestScore
                    FROM Users
                    ORDER BY HighestScore DESC
                    LIMIT 3";

                    using (var command = new SQLiteCommand(query, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string username = reader.GetString(0);
                                int highestScore = reader.GetInt32(1);
                                topScorers.Add((username, highestScore));
                            }
                        }
                    }
                }
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show($"数据库错误: {ex.Message}");
            }
            return topScorers;
        }
    }
}
